/**
 * Auto gen via dbpojo-gen.
 */
package com.us.usws.dao;

import java.util.List;

import net.iwsh.core.db.DBExecutor;
import net.iwsh.core.db.ext.DialectSyntaxParser;
import net.iwsh.core.db.ext.SQLWithParams;
import net.iwsh.core.utils.IDGenerator;

import com.us.usws.common.SiteConstants;
import com.us.usws.model.domain.video.VideoComment;
import com.us.usws.model.flat.video.DOVideoComment;
import com.us.usws.support.uitls.DataPageUtil;

/**
 * DAO class
 * @author robot
 */
public class VideoCommentDAO extends BasePageSearchDAO<VideoComment>{

	/**
	 * Query SQLs
	 */
	private static final String SQL_GET_TOTAL_COUNT = "select count(*) from hv_video_comment";

	private static final String SQL_GET_ALL_VIDEOCOMMENTS = "select * from hv_video_comment";

	private static final String SQL_GET_VIDEOCOMMENT_BY_PK = "select * from hv_video_comment where uid = ?";

	private static final String SQL_GET_COMMENTS_BY_VIDEO = "select * from hv_video_comment where video_uid=? and is_valid=? order by create_date";

	private static final String SQL_GET_GOOD_VOTE_CNT_BY_VIDEO_RATE = "select count(*) from hv_video_comment where video_uid=? and rate_score>0 and is_valid=?";
	private static final String SQL_GET_BAD_VOTE_CNT_BY_VIDEO_RATE = "select count(*) from hv_video_comment where video_uid=? and rate_score<=0 and is_valid=?";

	/**
	 * CUR SQLs
	 */
	private static final String SQL_INSERT_VIDEOCOMMENT = "insert into hv_video_comment(uid,video_uid,user_uid,rate_score,description,create_date,last_upd_date,is_valid) values (?,?,?,?,?,?,?,?)";

	private static final String SQL_UPDATE_VIDEOCOMMENT = "update hv_video_comment set video_uid=?, user_uid=?, rate_score=?, description=?, last_upd_date=?, is_valid=? where uid=?";

	private static final String SQL_DEL_VIDEOCOMMENT = "delete from hv_video_comment where uid=?";

	/**
	 * DBExeutor
	 */
	private DBExecutor dbe = new DBExecutor();

	/**
	 * Get Count.
	 */
	public int getTotalCount() {
		return dbe.getCount(SQL_GET_TOTAL_COUNT);
	}

	/**
	 * Get all list.
	 */
	public List<VideoComment> getAllVideoComments() {
		return dbe.list(SQL_GET_ALL_VIDEOCOMMENTS, VideoComment.class);
	}
	
	public void loadVideoCommentsByPage(DataPageUtil<VideoComment> dataPageUtil) {
		String countSql = "select count(*) from hv_video_comment where 1=1";
		String sql = " select * from hv_video_comment where 1=1";
		SQLWithParams swp = initSQLWithParams(dataPageUtil, null, countSql, sql);
		dataPageUtil.setResult(dbe.list(swp.getSql(), VideoComment.class, swp.getParams()));
	}

	/**
	 * Get all list by pagination.
	 */
	public List<VideoComment> getVideoComments(int start, int end) {
		SQLWithParams swp = DialectSyntaxParser.getParser().getPaginationSQLWithParams(SQL_GET_ALL_VIDEOCOMMENTS, null, start, end);
		return dbe.list(swp.getSql(), VideoComment.class, swp.getParams());
	}

	/**
	 * Get VideoComment by primary key.
	 */
	public VideoComment getVideoComment(String uid) {
		VideoComment v = dbe.load(SQL_GET_VIDEOCOMMENT_BY_PK, VideoComment.class, uid);
		return v;
	}

	/**
	 * Insert one row.
	 */
	public String insertVideoComment(DOVideoComment videoComment) {
		String uid = IDGenerator.getInstance().genUID();
		videoComment.setUid(uid);
		dbe.execute(SQL_INSERT_VIDEOCOMMENT, videoComment.getUid(), videoComment.getVideoUid(), videoComment.getUserUid(),
				videoComment.getRateScore(), videoComment.getDescription(), videoComment.getCreateDate(), videoComment.getLastUpdDate(),
				videoComment.getIsValid());
		return uid;
	}

	/**
	 * Update by PK.
	 */
	public void updateVideoComment(DOVideoComment videoComment) {
		dbe.execute(SQL_UPDATE_VIDEOCOMMENT, videoComment.getVideoUid(), videoComment.getUserUid(), videoComment.getRateScore(),
				videoComment.getDescription(), videoComment.getLastUpdDate(), videoComment.getIsValid(),
				videoComment.getUid());
	}

	/**
	 * Delete row by PK.
	 */
	public void deleteVideoComment(String uid) {
		dbe.execute(SQL_DEL_VIDEOCOMMENT, uid);
	}

	/**
	 * get count
	 * @param videoUid
	 * @return
	 */
	public int getCommentsCountByVideo(String videoUid) {
		String sql = DialectSyntaxParser.getParser().getCountSQL(SQL_GET_COMMENTS_BY_VIDEO);
		return dbe.getCount(sql, videoUid, SiteConstants.STR_BOOLEAN_YES);
	}

	/**
	 * get list by video, using pagination
	 * @param videoUid
	 * @param start
	 * @param end
	 * @return
	 */
	public List<VideoComment> getCommentsByVideo(String videoUid, int start, int end) {
		SQLWithParams swp = DialectSyntaxParser.getParser().getPaginationSQLWithParams(SQL_GET_COMMENTS_BY_VIDEO,
				new Object[] { videoUid, SiteConstants.STR_BOOLEAN_YES }, start, end);
		return dbe.list(swp.getSql(), VideoComment.class, swp.getParams());
	}

	/**
	 * get good vote
	 * @param videoUid
	 * @return
	 */
	public int getGoodVoteCntByVideo(String videoUid) {
		return dbe.getCount(SQL_GET_GOOD_VOTE_CNT_BY_VIDEO_RATE, videoUid, SiteConstants.STR_BOOLEAN_YES);
	}

	/**
	 * get bad vote
	 * @param videoUid
	 * @return
	 */
	public int getBadVoteCntByVideo(String videoUid) {
		return dbe.getCount(SQL_GET_BAD_VOTE_CNT_BY_VIDEO_RATE, videoUid, SiteConstants.STR_BOOLEAN_YES);
	}

}